package com.jxmcloud.business.cut.util;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;
import com.jxmcloud.business.cut.model.activity.Bag;
import com.jxmcloud.business.cut.vo.GoodsPoolRecordVo;
import com.jxmcloud.business.cut.vo.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.core.io.ClassPathResource;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.nio.charset.StandardCharsets;
import java.sql.Date;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Set;
import java.util.stream.Collectors;

/**
 * Excel导出工具类
 */
public class ExcelExportUtil {
    
    private static final Logger logger = LoggerFactory.getLogger(ExcelExportUtil.class);

    /**
     * 导出商品池记录到Excel
     *
     * @param response HTTP响应对象
     * @param records  商品池记录列表
     * @param fileName 导出文件名
     * @throws IOException IO异常
     */
    public static void exportGoodsPoolRecords(HttpServletResponse response, List<GoodsPoolRecordVo> records, String fileName) throws IOException {
        // 加载模板文件
        InputStream templateStream = null;
        try {
            // 尝试从classpath加载模板
            templateStream = ExcelExportUtil.class.getClassLoader().getResourceAsStream("template/商品池记录.xls");
            
            if (templateStream == null) {
                // 如果classpath中没有找到，尝试从其他位置加载
                logger.warn("未在classpath中找到模板文件，尝试从其他位置加载");
                templateStream = new ClassPathResource("template/商品池记录.xls").getInputStream();
            }
            
            if (templateStream == null) {
                throw new IOException("无法找到模板文件: template/商品池记录.xls");
            }
            
            // 创建工作簿
            Workbook workbook = WorkbookFactory.create(templateStream);
            Sheet sheet = workbook.getSheetAt(0);
            
            // 创建单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);
            
            // 填充数据
            int rowIndex = 1; // 从第3行开始填充数据（表头在第1、2行）
            for (GoodsPoolRecordVo record : records) {
                // 直接使用现有行或创建新行，避免中间出现空行
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                rowIndex++;

                // 编号
                createCell(row, 0, String.valueOf(rowIndex-1), cellStyle);
                
                // 类型
                createCell(row, 1, record.getType() != null ? (record.getSendStatus() == 1 ? "支出" : "收入") : "", cellStyle);
                
                // 商品名称
                createCell(row, 2, record.getGoodsName(), cellStyle);
                
                // 商品编码
                createCell(row, 3, record.getGoodsCode(), cellStyle);
                
                // 数量
                createCell(row, 4, record.getNum() != null ? record.getNum().toString() : "", cellStyle);
                
                // 状态
                createCell(row, 5, record.getSendStatus()  != null ? (record.getSendStatus() == 1 ? "发放成功" : "发放失败") : "", cellStyle);
                
                // 原因
                createCell(row, 6, record.getReason(), cellStyle);
                
                // 玄乐账号
                createCell(row, 7, record.getLoginName(), cellStyle);
                
                // 商品剩余数量
                createCell(row, 8, record.getRemainNum() != null ? record.getRemainNum().toString() : "", cellStyle);
                
                // 账号商品统计
                createCell(row, 9, record.getAccountGoodsTotal() != null ? record.getAccountGoodsTotal().toString() : "", cellStyle);
            }
            
            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            // 修改Content-Disposition头，解决文件名乱码问题
            response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName + ".xls;filename*=utf-8''" + encodedFileName + ".xls");
            
            // 写入响应输出流
            try (OutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            } finally {
                workbook.close();
            }
            
        } catch (IOException e) {
            logger.error("导出Excel失败", e);
            throw e;
        } catch (InvalidFormatException e) {
            throw new RuntimeException(e);
        } finally {
            if (templateStream != null) {
                try {
                    templateStream.close();
                } catch (IOException e) {
                    logger.error("关闭模板文件流失败", e);
                }
            }
        }
    }

    /**
     * 导出砍一刀活动记录到Excel
     *
     * @param response HTTP响应对象
     * @param records  用户活动信息列表
     * @param fileName 导出文件名
     * @throws IOException IO异常
     */
    public static void exportUserActivityRecords(HttpServletResponse response, List<UserActivityInfoVo> records, String fileName) throws IOException {
        // 加载模板文件
        InputStream templateStream = null;
        try {
            // 尝试从classpath加载模板
            templateStream = ExcelExportUtil.class.getClassLoader().getResourceAsStream("template/砍一刀.xls");

            if (templateStream == null) {
                // 如果classpath中没有找到，尝试从其他位置加载
                logger.warn("未在classpath中找到模板文件，尝试从其他位置加载");
                templateStream = new ClassPathResource("template/砍一刀.xls").getInputStream();
            }

            if (templateStream == null) {
                throw new IOException("无法找到模板文件: template/砍一刀.xls");
            }

            // 创建工作簿
            Workbook workbook = WorkbookFactory.create(templateStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 创建单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            // 填充数据
            int rowIndex = 1; // 从第2行开始填充数据（表头只在第1行）
            for (UserActivityInfoVo record : records) {
                // 直接使用现有行或创建新行，避免中间出现空行
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                rowIndex++;

                // 编号
                createCell(row, 0, String.valueOf(rowIndex-1), cellStyle);

                // 客户姓名
                createCell(row, 1, record.getNickName(), cellStyle);

                // 时间
                createCell(row, 2,  sdf.format(new Date(record.getCreateTime() * 1000L)), cellStyle);

                // 活动ID
                createCell(row, 3, record.getActivityId() != null ? record.getActivityId().toString() : "", cellStyle);

                // 活动金额
                createCell(row, 4, record.getGold() != null ? record.getGold().toString() : "", cellStyle);

                // 玄乐账号
                createCell(row, 5, record.getAccount(), cellStyle);

                // 隐藏池子分
                createCell(row, 6, record.getScorePool() != null ? record.getScorePool().toString() : "", cellStyle);
                // 当前道具数量（优化为展示每个道具的名称和数量）
                Set<Bag> bagSet = record.getBagSet();
                String bagInfo = "0";
                if (!CollectionUtils.isEmpty(bagSet)) {
                    bagInfo = bagSet.stream()
                            .map(bag -> bag.getName() + bag.getV())  // 将每个道具的名称和数量拼接
                            .collect(Collectors.joining(", "));  // 使用逗号和空格连接
                }
                // 当前道具数量
                createCell(row, 7, record.getBagSet() != null ? bagInfo : "0", cellStyle);

                // 拉人数量
                createCell(row, 8, record.getInvitedCount() != null ? record.getInvitedCount().toString() : "", cellStyle);

                // 拉新人数量
                createCell(row, 9, record.getNewUserCount() != null ? record.getNewUserCount().toString() : "", cellStyle);

                // 是否完成观看直播任务
                createCell(row, 10, record.getWatchLive() != null ? (record.getWatchLive() == 1 ? "是" : "否") : "", cellStyle);

                // 是否领奖
                createCell(row, 11, record.getGainPrize() != null ? (record.getGainPrize() == 1 ? "是" : "否") : "", cellStyle);
            }

            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            // 修改Content-Disposition头，解决文件名乱码问题
            response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName + ".xls;filename*=utf-8''" + encodedFileName + ".xls");

            // 写入响应输出流
            try (OutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            } finally {
                workbook.close();
            }

        } catch (IOException e) {
            logger.error("导出Excel失败", e);
            throw e;
        } catch (InvalidFormatException e) {
            throw new RuntimeException(e);
        } finally {
            if (templateStream != null) {
                try {
                    templateStream.close();
                } catch (IOException e) {
                    logger.error("关闭模板文件流失败", e);
                }
            }
        }
    }

    /**
     * 导出奖金池记录到Excel
     *
     * @param response HTTP响应对象
     * @param records  奖金池记录列表
     * @param fileName 导出文件名
     * @throws IOException IO异常
     */
    public static void exportRewardPoolRecords(HttpServletResponse response, List<RewardPoolRecordVo> records, String fileName) throws IOException {
        // 加载模板文件
        InputStream templateStream = null;
        try {
            // 尝试从classpath加载模板
            templateStream = ExcelExportUtil.class.getClassLoader().getResourceAsStream("template/奖金池记录.xls");

            if (templateStream == null) {
                // 如果classpath中没有找到，尝试从其他位置加载
                logger.warn("未在classpath中找到模板文件，尝试从其他位置加载");
                templateStream = new ClassPathResource("template/奖金池记录.xls").getInputStream();
            }

            if (templateStream == null) {
                throw new IOException("无法找到模板文件: template/奖金池记录.xls");
            }

            // 创建工作簿
            Workbook workbook = WorkbookFactory.create(templateStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 创建单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
            // 填充数据
            int rowIndex = 1; // 从第2行开始填充数据（表头只在第1行）
            int counter = 1; // 编号从1开始

            for (RewardPoolRecordVo record : records) {
                // 直接使用现有行或创建新行，避免中间出现空行
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                rowIndex++;

                // 编号
                createCell(row, 0, String.valueOf(counter++), cellStyle);

                // 类型
                createCell(row, 1, record.getType() != null ? (record.getType() == 1 ? "支出" : "收入") : "", cellStyle);

                // 金额
                createCell(row, 2, record.getMoney() != null ? record.getMoney().toString() : "", cellStyle);

                // 状态
                createCell(row, 3, record.getStatus() != null ? (record.getStatus() == 1 ? "已发放" : "已到账") : "", cellStyle);

                // 原因
                createCell(row, 4, record.getReason(), cellStyle);

                // 玄乐账号
                createCell(row, 5, record.getLoginName(), cellStyle);

                // 剩余金额
                createCell(row, 6, record.getRemainAmount() != null ? record.getRemainAmount().toString() : "", cellStyle);

                // 时间
                createCell(row, 7, sdf.format(new Date(record.getCreateTime() * 1000L)), cellStyle);
            }

            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            // 修改Content-Disposition头，解决文件名乱码问题
            response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName + ".xls;filename*=utf-8''" + encodedFileName + ".xls");

            // 写入响应输出流
            try (OutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            } finally {
                workbook.close();
            }

        } catch (IOException e) {
            logger.error("导出Excel失败", e);
            throw e;
        } catch (InvalidFormatException e) {
            throw new RuntimeException(e);
        } finally {
            if (templateStream != null) {
                try {
                    templateStream.close();
                } catch (IOException e) {
                    logger.error("关闭模板文件流失败", e);
                }
            }
        }
    }

    /**
     * 导出拉新记录到Excel
     *
     * @param response HTTP响应对象
     * @param records  拉新记录列表
     * @param fileName 导出文件名
     * @throws IOException IO异常
     */
    public static void exportInviteRecords(HttpServletResponse response, List<InviteSuccessVo> records, String fileName) throws IOException {
        // 加载模板文件
        InputStream templateStream = null;
        try {
            // 尝试从classpath加载模板
            templateStream = ExcelExportUtil.class.getClassLoader().getResourceAsStream("template/拉新记录.xls");

            if (templateStream == null) {
                // 如果classpath中没有找到，尝试从其他位置加载
                logger.warn("未在classpath中找到模板文件，尝试从其他位置加载");
                templateStream = new ClassPathResource("template/拉新记录.xls").getInputStream();
            }

            if (templateStream == null) {
                throw new IOException("无法找到模板文件: template/拉新记录.xls");
            }

            // 创建工作簿
            Workbook workbook = WorkbookFactory.create(templateStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 创建单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            // 填充数据
            int rowIndex = 1; // 从第2行开始填充数据（表头只在第1行）
            int counter = 1; // 编号从1开始

            for (InviteSuccessVo record : records) {
                // 直接使用现有行或创建新行，避免中间出现空行
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                rowIndex++;

                // 编号
                createCell(row, 0, String.valueOf(counter++), cellStyle);

                // 被邀请客户姓名
                createCell(row, 1, record.getInvitedName(), cellStyle);

                // 被邀请玄乐账号
                createCell(row, 2, record.getInvitedAccount(), cellStyle);

                // 活动ID
                createCell(row, 3, record.getActivityId() != null ? record.getActivityId().toString() : "", cellStyle);

                // 是否为新用户
                createCell(row, 4, record.getUserType() != null ? (record.getUserType() == 1 ? "是" : "否") : "", cellStyle);
            }

            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            // 修改Content-Disposition头，解决文件名乱码问题
            response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName + ".xls;filename*=utf-8''" + encodedFileName + ".xls");

            // 写入响应输出流
            try (OutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            } finally {
                workbook.close();
            }

        } catch (IOException e) {
            logger.error("导出Excel失败", e);
            throw e;
        } catch (InvalidFormatException e) {
            throw new RuntimeException(e);
        } finally {
            if (templateStream != null) {
                try {
                    templateStream.close();
                } catch (IOException e) {
                    logger.error("关闭模板文件流失败", e);
                }
            }
        }
    }

    /**
     * 导出礼包购买记录到Excel
     *
     * @param response HTTP响应对象
     * @param records  礼包购买记录列表
     * @param fileName 导出文件名
     * @throws IOException IO异常
     */
    public static void exportGiftBuyRecords(HttpServletResponse response, List<GiftBuyRecordVo> records, String fileName) throws IOException {
        // 加载模板文件
        InputStream templateStream = null;
        try {
            // 尝试从classpath加载模板
            templateStream = ExcelExportUtil.class.getClassLoader().getResourceAsStream("template/礼包购买记录.xls");

            if (templateStream == null) {
                // 如果classpath中没有找到，尝试从其他位置加载
                logger.warn("未在classpath中找到模板文件，尝试从其他位置加载");
                templateStream = new ClassPathResource("template/礼包购买记录.xls").getInputStream();
            }

            if (templateStream == null) {
                throw new IOException("无法找到模板文件: template/礼包购买记录.xls");
            }

            // 创建工作簿
            Workbook workbook = WorkbookFactory.create(templateStream);
            Sheet sheet = workbook.getSheetAt(0);

            // 创建单元格样式
            CellStyle cellStyle = workbook.createCellStyle();
            cellStyle.setBorderTop(BorderStyle.THIN);
            cellStyle.setBorderRight(BorderStyle.THIN);
            cellStyle.setBorderBottom(BorderStyle.THIN);
            cellStyle.setBorderLeft(BorderStyle.THIN);
            cellStyle.setAlignment(HorizontalAlignment.CENTER);
            cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);

            SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

            // 填充数据
            int rowIndex = 1; // 从第2行开始填充数据（表头只在第1行）
            int counter = 1; // 编号从1开始

            for (GiftBuyRecordVo record : records) {
                // 直接使用现有行或创建新行，避免中间出现空行
                Row row = sheet.getRow(rowIndex);
                if (row == null) {
                    row = sheet.createRow(rowIndex);
                }
                rowIndex++;

                // 编号
                createCell(row, 0, String.valueOf(counter++), cellStyle);

                // 账号姓名
                createCell(row, 1, record.getUsername(), cellStyle);

                // 玄乐账号
                createCell(row, 2, record.getLoginName(), cellStyle);

                // 活动ID
                createCell(row, 3, record.getActivityId() != null ? record.getActivityId().toString() : "", cellStyle);

                // 购买礼包金额
                createCell(row, 4, record.getBuyMoney() != null ? record.getBuyMoney().toString() : "", cellStyle);

                // 优惠券名称 - 处理多个优惠券情况
                StringBuilder couponNameBuilder = new StringBuilder();
                if (!StringUtils.isEmpty(record.getCoupon())) {
                    try {
                        // 使用FastJSON解析JSON字符串
                        JSONArray couponArray = JSON.parseArray(record.getCoupon());
                        if (couponArray != null && !couponArray.isEmpty()) {
                            for (int i = 0; i < couponArray.size(); i++) {
                                JSONObject couponObj = couponArray.getJSONObject(i);
                                if (couponObj.containsKey("couponName")) {
                                    // 添加换行符，但第一条不添加
                                    if (i > 0) {
                                        couponNameBuilder.append("\n");
                                    }
                                    couponNameBuilder.append(couponObj.getString("couponName"));
                                }
                            }
                        }
                    } catch (Exception e) {
                        logger.error("解析优惠券JSON失败: " + record.getCoupon(), e);
                        couponNameBuilder.append(record.getCoupon()); // 如果解析失败，使用原始字符串
                    }
                }
                String couponName = couponNameBuilder.toString();

                // 优惠券名称
                createCell(row, 5, couponName, cellStyle);

                // 实付金额
                createCell(row, 6, record.getActualMoney() != null ? record.getActualMoney().toString() : "", cellStyle);

                // 购买时间
                createCell(row, 7, sdf.format(new Date(record.getCreateTime() * 1000L)), cellStyle);
            }

            // 设置响应头
            response.setContentType("application/vnd.ms-excel");
            response.setCharacterEncoding("utf-8");
            String encodedFileName = URLEncoder.encode(fileName, StandardCharsets.UTF_8.toString());
            // 修改Content-Disposition头，解决文件名乱码问题
            response.setHeader("Content-Disposition", "attachment;filename=" + encodedFileName + ".xls;filename*=utf-8''" + encodedFileName + ".xls");

            // 写入响应输出流
            try (OutputStream outputStream = response.getOutputStream()) {
                workbook.write(outputStream);
                outputStream.flush();
            } finally {
                workbook.close();
            }

        } catch (IOException e) {
            logger.error("导出Excel失败", e);
            throw e;
        } catch (InvalidFormatException e) {
            throw new RuntimeException(e);
        } finally {
            if (templateStream != null) {
                try {
                    templateStream.close();
                } catch (IOException e) {
                    logger.error("关闭模板文件流失败", e);
                }
            }
        }
    }
    /**
     * 创建单元格并设置值
     *
     * @param row       行对象
     * @param colIndex  列索引
     * @param value     单元格值
     * @param cellStyle 单元格样式
     */
    private static void createCell(Row row, int colIndex, String value, CellStyle cellStyle) {
        Cell cell = row.createCell(colIndex);
        cell.setCellValue(value);
        cell.setCellStyle(cellStyle);
        // 设置自动换行属性
        CellStyle newStyle = row.getSheet().getWorkbook().createCellStyle();
        newStyle.cloneStyleFrom(cellStyle);
        newStyle.setWrapText(true);
        cell.setCellStyle(newStyle);
    }
}